from sqlalchemy import create_engine, inspect, text
import pandas as pd

local_db_url = 'mysql+pymysql://root:root@127.0.0.1:3306/xuefa_db?charset=utf8mb4'
remote_db_url = 'mysql+pymysql://xuefa_db:root@118.31.69.88:3306/xuefa_db?charset=utf8mb4'


def get_all_tables(engine):
    inspector = inspect(engine)
    return inspector.get_table_names()


def get_table_structure(engine, table):
    inspector = inspect(engine)
    columns = inspector.get_columns(table)
    structure = [(col['name'], str(col['type'])) for col in columns]
    return structure


def get_table_data(engine, table, limit=1000):
    query = text(f"SELECT * FROM {table} LIMIT {limit}")
    df = pd.read_sql(query, engine)
    return df


def compare_tables(local_engine, remote_engine):
    local_tables = set(get_all_tables(local_engine))
    remote_tables = set(get_all_tables(remote_engine))

    print("本地库表数:", len(local_tables))
    print("远程库表数:", len(remote_tables))

    # 比较两个库的表差异
    print("本地独有表:", local_tables - remote_tables)
    print("远程独有表:", remote_tables - local_tables)

    # 交集表做结构和数据对比
    common_tables = local_tables & remote_tables
    for table in common_tables:
        print(f"\n对比表: {table}")

        local_struct = get_table_structure(local_engine, table)
        remote_struct = get_table_structure(remote_engine, table)

        local_cols = set([c[0] for c in local_struct])
        remote_cols = set([c[0] for c in remote_struct])

        print("  本地多出的字段:", local_cols - remote_cols)
        print("  远程多出的字段:", remote_cols - local_cols)


def main():
    local_engine = create_engine(local_db_url)
    remote_engine = create_engine(remote_db_url)

    compare_tables(local_engine, remote_engine)


import pymysql


# def db_edit():
#     # 配置数据库连接
#     conn = pymysql.connect(
#         host='localhost',
#         port=3306,
#         user='root',
#         password='root',
#         database='xuefa_db',
#         charset='utf8mb4'
#     )
#     print("连接到数据库成功")
#     cursor = conn.cursor()
#
#     # 查询所有表
#     cursor.execute("""
#         SELECT table_name
#         FROM information_schema.tables
#         WHERE table_schema = %s
#           AND table_type = 'BASE TABLE'
#           AND table_name NOT LIKE 'flyway%%'
#           AND table_name NOT LIKE 'sys_%%'
#           AND table_name != 'tenant'
#     """, ('xuefa_db',))
#
#     tables = cursor.fetchall()
#
#     for (table_name,) in tables:
#         print(f"\n处理表：{table_name}")
#
#         try:
#             # 检查是否已有 tenant_id 字段
#             cursor.execute("""
#                 SELECT COUNT(*)
#                 FROM information_schema.columns
#                 WHERE table_schema = %s AND table_name = %s AND column_name = 'tenant_id'
#             """, ('xuefa_db', table_name))
#
#             if cursor.fetchone()[0] > 0:
#                 print(f"表 {table_name} 已有 tenant_id 字段，跳过")
#                 continue
#
#             # 添加字段
#             alter_sql = f"ALTER TABLE `{table_name}` ADD COLUMN `tenant_id` BIGINT NOT NULL DEFAULT 0;"
#             cursor.execute(alter_sql)
#             print(f"✅ 已为表 {table_name} 添加字段 tenant_id")
#
#             # 添加索引
#             index_sql = f"CREATE INDEX idx_{table_name}_tenant_id ON `{table_name}` (`tenant_id`);"
#             cursor.execute(index_sql)
#             print(f"✅ 已为表 {table_name} 添加索引 idx_{table_name}_tenant_id")
#
#         except Exception as e:
#             print(f"❌ 表 {table_name} 执行失败: {e}")
#
#     # 提交 & 关闭
#     conn.commit()
#     cursor.close()
#     conn.close()
#     print("\n🎉 所有表处理完成 ✅")


# def db_edit():
#     # 配置数据库连接
#     conn = pymysql.connect(
#         host='118.31.69.88',
#         port=3306,
#         user='xuefa_db',
#         password='root',
#         database='xuefa_db',
#         charset='utf8mb4'
#     )
#     print("连接到数据库成功")
#     cursor = conn.cursor()
#
#     # 查询所有表
#     cursor.execute("""
#         SELECT table_name
#         FROM information_schema.tables
#         WHERE table_schema = %s
#           AND table_type = 'BASE TABLE'
#           AND table_name NOT LIKE 'flyway%%'
#           AND table_name NOT LIKE 'sys_%%'
#           AND table_name != 'tenant'
#     """, ('xuefa_db',))
#
#     tables = cursor.fetchall()
#
#     for (table_name,) in tables:
#         print(f"\n处理表：{table_name}")
#
#         try:
#             # 检查是否已有 tenant_id 字段
#             cursor.execute("""
#                 SELECT COUNT(*)
#                 FROM information_schema.columns
#                 WHERE table_schema = %s AND table_name = %s AND column_name = 'tenant_id'
#             """, ('xuefa_db', table_name))
#
#             if cursor.fetchone()[0] == 0:
#                 print(f"❌ 表 {table_name} 没有 tenant_id 字段，跳过")
#                 continue  # 如果表中没有 tenant_id 字段，跳过
#
#             # 修改 tenant_id 字段的默认值
#             alter_sql = f"ALTER TABLE `{table_name}` ALTER COLUMN `tenant_id` SET DEFAULT 1;"
#             cursor.execute(alter_sql)
#             print(f"✅ 已为表 {table_name} 修改字段 tenant_id 的默认值为 1")
#
#         except Exception as e:
#             print(f"❌ 表 {table_name} 执行失败: {e}")
#
#     # 提交 & 关闭
#     conn.commit()
#     cursor.close()
#     conn.close()
#     print("\n🎉 所有表处理完成 ✅")


def db_edit():
    # 配置数据库连接
    conn = pymysql.connect(
        host='118.31.69.88',
        port=3306,
        user='xuefa_db',
        password='root',
        database='xuefa_db',
        charset='utf8mb4'
    )
    print("连接到数据库成功")
    cursor = conn.cursor()

    # 查询所有包含 tenant_id 字段的表
    cursor.execute(""" 
        SELECT table_name
        FROM information_schema.columns
        WHERE table_schema = %s
          AND column_name = 'tenant_id'
          AND table_name NOT LIKE 'flyway%%'
          AND table_name NOT LIKE 'sys_%%'
          AND table_name != 'tenant'
    """, ('xuefa_db',))

    tables = cursor.fetchall()

    for (table_name,) in tables:
        print(f"\n处理表：{table_name}")

        try:
            # 更新所有记录中的 tenant_id 为 1
            update_sql = f"UPDATE `{table_name}` SET `tenant_id` = 1;"
            cursor.execute(update_sql)
            print(f"✅ 已为表 {table_name} 更新所有记录的 tenant_id 为 1")

        except Exception as e:
            print(f"❌ 表 {table_name} 更新失败: {e}")

    # 提交 & 关闭
    conn.commit()
    cursor.close()
    conn.close()
    print("\n🎉 所有表处理完成 ✅")

if __name__ == '__main__':
    db_edit()
